package org.zh.sh.util.create;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.List;

public class SQLiteTablesInfo implements TablesInfo {

    public static final  SQLiteTablesInfo info = new SQLiteTablesInfo();
    @Override
    public List<String> showTables(Connection conn) throws SQLException {
        List<String> list = new ArrayList<>();
        String sql = "SELECT name FROM sqlite_master WHERE type='table'";
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()) {
            list.add(rs.getString(1));
        }
        rs.close();
        st.close();
        return list;
    }

    @Override
    public List<TableInfo> allColumnsInfo(Collection<String> tableNames, Connection conn) throws SQLException {
        List<TableInfo> tas = new ArrayList<>();
        Statement ps = null;
        ResultSet rs = null;
        for (String tb : tableNames) {
            String sql = "PRAGMA table_info( '" + tb + "' )";
            ps = conn.createStatement();
            rs = ps.executeQuery(sql);
            List<ColumnInfo> list = new ArrayList<>();
            while (rs.next()) {
                String COLUMN_NAME = rs.getString(2);
                String COLUMN_TYPE = rs.getString(3);
                String COLUMN_COMMENT = "";
                int COLUMN_KEY = rs.getInt(6);
                int EXTRA = rs.getInt(6);
                ColumnInfo info = new ColumnInfo(COLUMN_NAME, COLUMN_TYPE, SQLiteJavaType(COLUMN_TYPE), clazzAlias(COLUMN_NAME), COLUMN_COMMENT, COLUMN_KEY == 1, EXTRA == 1);
                list.add(info);
            }
            TableInfo tableInfo = new TableInfo(tb, clazzName(tb), clazzAlias(tb), list);
            tas.add(tableInfo);
        }
        assert ps != null;
        rs.close();
        ps.close();
        return tas;
    }

    @Override
    public TableInfo columnsInfo(String tableName, Connection conn) throws SQLException {
        return allColumnsInfo(Collections.singleton(tableName),conn).getFirst();
    }

    @Override
    public List<String> tablePri(String tableName, Connection conn) throws SQLException {
        TableInfo tableInfo = columnsInfo(tableName, conn);
        List<String> list = new ArrayList<>();
        for (ColumnInfo columnInfo:tableInfo.columns()){
            if(columnInfo.autoincrement()){
                list.add(columnInfo.columnName());
            }
        }
        return list;
    }

    private String SQLiteJavaType(String COLUMN_TYPE) {
        Class<?> type = switch (COLUMN_TYPE) {
            case "INTEGER", "int", "INT", "integer", "BIGINT", "bigint", "SMALLINT", "smallint" -> Integer.class;
            case "NUMERIC", "numeric", "FLOAT", "float", "DECIMAL", "decimal", "DOUBLE", "double", "REAL", "real" ->
                    BigDecimal.class;
            case "VARCHAR", "varchar", "CHAR", "char", "TEXT", "text" -> String.class;
            case "CLOB", "clob" -> byte[].class;
            default -> Object.class;
        };
        return type.getName();
    }


}
